﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AcadPos.Entity;
using TrustX.Dados;
using System.Data;
using System.Data.SqlClient;

namespace AcadPos.Data
{
    public class DBDisciplina
    {
        #region Inserir
        public bool inserir(Disciplina disciplina, string usuario)
        {
            string SQL = @"INSERT INTO TB_DISCIPLINA
                                  (
                                    DESCRICAO,
                                    DATA_CADASTRO
                                  )
                                VALUES
                                  (
                                    UPPER(@DESCRICAO),
                                    GETDATE()
                                  )";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@DESCRICAO", SqlDbType.VarChar, disciplina.Descricao);
            //return db.ExecuteNonQuery();
            
            bool inseriu = db.ExecuteNonQuery();
            
            if (inseriu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'INSERT',
                        'TB_DISCIPLINA',
                        ((SELECT CAST(MAX(ID_DISCIPLINA) AS VARCHAR) FROM TB_DISCIPLINA)
                        + ' | ' + UPPER(@DESCRICAO)), 
                        UPPER(@USUARIO),
                        GETDATE()
                      )";

                db = new DataAccess(SQL);

                db.AddParam("@DESCRICAO", SqlDbType.VarChar, disciplina.Descricao);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return inseriu;
        }
        #endregion

        #region Alterar
        public bool alterar(Disciplina disciplina, string usuario)
        {
            string SQL = @"UPDATE  TB_DISCIPLINA
                            SET    DESCRICAO = UPPER(@DESCRICAO)
                            WHERE  ID_DISCIPLINA = @ID_DISCIPLINA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@DESCRICAO", SqlDbType.VarChar, disciplina.Descricao);
            db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, disciplina.IdDisciplina);
            //return db.ExecuteNonQuery();
            
            bool atualizou = db.ExecuteNonQuery();
            
            if (atualizou)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'UPDATE',
                        'TB_DISCIPLINA',
                        (CAST(@ID_DISCIPLINA AS VARCHAR)
                         + ' | ' + UPPER(@DESCRICAO)), 
                        UPPER(@USUARIO),
                        GETDATE()
                      )";
                db = new DataAccess(SQL);
                db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, disciplina.IdDisciplina);
                db.AddParam("@DESCRICAO", SqlDbType.VarChar, disciplina.Descricao);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return atualizou;

        }
        #endregion

        #region Excluir
        public bool excluir(Disciplina disciplina, string usuario)
        {
            string SQL = @"DELETE FROM   TB_DISCIPLINA
                                 WHERE  ID_DISCIPLINA = @ID_DISCIPLINA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, disciplina.IdDisciplina);
            db.AddParam("@DESCRICAO", SqlDbType.VarChar, disciplina.Descricao);
            //return db.ExecuteNonQuery();
            
            bool excluiu = db.ExecuteNonQuery();
            
            if (excluiu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'DELETE',
                        'TB_DISCIPLINA',
                        (CAST(@ID_DISCIPLINA AS VARCHAR)
                         + ' | ' + UPPER(@DESCRICAO)), 
                        UPPER(@USUARIO),
                        GETDATE()
                      )";
                db = new DataAccess(SQL);
                db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, disciplina.IdDisciplina);
                db.AddParam("@DESCRICAO", SqlDbType.VarChar, disciplina.Descricao);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }
            db.ExecuteNonQuery();
            return excluiu;
        }
        #endregion

        #region Listar Disciplina
        public DataTable listarDisciplina(Disciplina disciplina)
        {
            string SQL = @"SELECT  td.ID_DISCIPLINA,
                                   td.DESCRICAO
                            FROM   TB_DISCIPLINA td";

            if (disciplina.Descricao != "")
                SQL += "  WHERE  td.DESCRICAO LIKE '%' + @DESCRICAO + '%' ";

            if (disciplina.IdDisciplina > 0)
                SQL += "  WHERE  td.ID_DISCIPLINA = @ID_DISCIPLINA ";

            SQL += " ORDER BY td.DESCRICAO";

            DataAccess db = new DataAccess(SQL);
            if (disciplina.Descricao != "")
                db.AddParam("@DESCRICAO", SqlDbType.VarChar, disciplina.Descricao);
            if (disciplina.IdDisciplina > 0)
                db.AddParam("@ID_DISCIPLINA", SqlDbType.VarChar, disciplina.IdDisciplina);
            return db.GetDataTable();
        }
        #endregion

        #region Verificar Disciplina
        public DataTable disciplinaJaExiste(Disciplina disciplina)
        {
            string SQL = @"SELECT  td.ID_DISCIPLINA
                            FROM   TB_DISCIPLINA td ";

            if (disciplina.Descricao != "")
                SQL += "  WHERE  UPPER(td.DESCRICAO) = UPPER(@DESCRICAO)";

            DataAccess db = new DataAccess(SQL);
            if (disciplina.Descricao != "")
                db.AddParam("@DESCRICAO", SqlDbType.VarChar, disciplina.Descricao);            
            return db.GetDataTable();
        }
        #endregion

        #region Lista Disciplinas Por Professor
        public DataTable listaDisciplinasProfessor(Turma turma)
        {
            string SQL = @"SELECT  td.DESCRICAO   AS NOME_DISCIPLINA,
                                   tpt.DESCRICAO_TITULACAO,
                                   tnt.DESCRICAO     descricao_nivel_titulacao,
                                   TP.NOME        AS PROFISSIONAL,
                                   tpd.VALOR      AS valor_titulacao,
                                   tpd.ID_PROFISSIONAL_DISCIPLINA,
                                   tp.ID_PROFISSIONAL,
                                   tpd.ID_PROFISSIONAL_TITULACAO,
                                   tt.ID_TURMA,
                                   tmc2.ID_MATRIZ_CURRICULAR,
                                   td.ID_DISCIPLINA,
                                   tmc.ID_MODULO_CURRICULO,
                                   tpd.LIBERAR_MATRICULA,
                                   tpd.DATA_IMPRESSAO_DECLARACAO,
                                   tpd.DATA_IMPRESSAO_RECIBO, TP.CPF, TP.RG,
                                   CONVERT(VARCHAR,tpd.DATA_RECEPCAO_DIARIO,103) AS DATA_RECEPCAO_DIARIO, tmc2.CH_DISCIPLINA
                                   --CASE tdp.SITUACAO
                                   --     WHEN 'S' THEN 'Entregue'
                                   --     ELSE 'Pendente'
                                   --END AS SITUACAO_DOCUMENTACAO
                            FROM   TB_TURMAS tt
                                   INNER JOIN TB_MODULO_CURRICULO tmc
                                        ON  tmc.ID_CURRICULO = tt.ID_CURRICULO
                                        AND tmc.ID_MODULO = tt.ID_MODULO
                                   INNER JOIN TB_MATRIZ_CURRICULAR tmc2
                                        ON  tmc2.ID_MODULO_CURRICULO = tmc.ID_MODULO_CURRICULO
                                   INNER JOIN TB_DISCIPLINA td
                                        ON  td.ID_DISCIPLINA = tmc2.ID_DISCIPLINA
                                   LEFT JOIN TB_PROFISSIONAL_DISCIPLINA tpd
                                        ON  tt.ID_TURMA = tpd.ID_TURMA
                                        AND tpd.ID_DISCIPLINA = td.ID_DISCIPLINA
                                   LEFT JOIN TB_PROFISSIONAL_TITULACAO tpt
                                        ON  tpt.ID_PROFISSIONAL_TITULACAO = tpd.ID_PROFISSIONAL_TITULACAO
                                   LEFT JOIN TB_PROFISSIONAL tp
                                        ON  tpd.ID_PROFISSIONAL = tp.ID_PROFISSIONAL
                                   LEFT JOIN TB_NIVEL_TITULACAO tnt
                                        ON  tnt.ID_NIVEL_TITULACAO = tpt.ID_NIVEL_TITULACAO
                               --    LEFT JOIN TB_DOCUMENTO_PROFISSIONAL tdp 
			                            --ON tdp.ID_PROFISSIONAL = tp.ID_PROFISSIONAL AND tdp.  
                            WHERE  tt.ID_TURMA = @ID_TURMA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, turma.IdTurma);
            return db.GetDataTable();
        }
        #endregion

        #region Lista Professores Apto Disciplina
        public DataTable listaProfessorAptosDisc(int idDisciplina, string nome)
        {
            string SQL = @"SELECT  tp.ID_PROFISSIONAL,
                                   tp.NOME,                                   
                                   tp.SITUACAO
                            FROM   TB_PROFISSIONAL_DISCIPLINA_APTO_MINISTRAR tpdam
                                   INNER JOIN TB_PROFISSIONAL tp
                                        ON  tp.ID_PROFISSIONAL = tpdam.ID_PROFISSIONAL                                   
                            WHERE  tpdam.ID_DISCIPLINA = @ID_DISCIPLINA
                                   AND tp.NOME LIKE @NOME + '%'";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, idDisciplina);
            db.AddParam("@NOME", SqlDbType.VarChar, nome);
            return db.GetDataTable();
        }
        #endregion

        #region Insere Disc Por Professor
        public string inserirDiscProfessor(ProfissionalDisciplina profDisc, int qtdDisc, string usuario)
        {
            string SQL = @"exec SP_INSERE_PROFISSIONAL_DISCIPLINA
                                    @ID_PROFISSIONAL,
	                                @ID_PROFISSIONAL_TITULACAO,
	                                @ID_TURMA,
	                                @ID_MATRIZ_CURRICULAR,
	                                @ID_DISCIPLINA,
	                                @QTD_TOTAL_DISCIPLINAS

                                    INSERT INTO TB_AUDITORIA
                                      (
                                        ACAO,
                                        TABELA,
                                        DESCRICAO,
                                        USUARIO,
                                        DATA                                    
                                      )
                                    VALUES
                                      (                                     
                                        'INSERT',
                                        'PROFISSIONAL_DISCIPLINA',
                                        (CAST(@ID_PROFISSIONAL AS VARCHAR)
                                        + ' | ' + CAST(@ID_PROFISSIONAL_TITULACAO AS VARCHAR)
                                        + ' | ' + CAST(@ID_TURMA AS VARCHAR)
                                        + ' | ' + CAST(@ID_MATRIZ_CURRICULAR AS VARCHAR)
                                        + ' | ' + CAST(@ID_DISCIPLINA AS VARCHAR)
                                        + ' | ' + CAST(@QTD_TOTAL_DISCIPLINAS AS VARCHAR)), 
                                        UPPER(@USUARIO),
                                        GETDATE()
                                      );";   

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, profDisc.IdProfissionalDisciplina);
            db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, profDisc.IdProfissional);
            db.AddParam("@ID_PROFISSIONAL_TITULACAO", SqlDbType.Int, profDisc.IdProfissionalTitulacao);
            db.AddParam("@ID_TURMA", SqlDbType.Int, profDisc.IdTurma);
            db.AddParam("@ID_MATRIZ_CURRICULAR", SqlDbType.Int, profDisc.IdMatrizCurricular);
            db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, profDisc.IdDisciplina);
            db.AddParam("@QTD_TOTAL_DISCIPLINAS", SqlDbType.Int, qtdDisc);
            db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);

            DataTable dt = db.GetDataTable();
            return dt.Rows[0][0].ToString();
        }
        #endregion

        #region Excluir Disciplina por Professor
        public bool excluirDiscProfessor(ProfissionalDisciplina profDisc, string usuario)
        {
            string SQL = @"DELETE 
                                FROM   TB_PROFISSIONAL_DISCIPLINA
                                WHERE  ID_PROFISSIONAL_DISCIPLINA = @ID_PROFISSIONAL_DISCIPLINA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, profDisc.IdProfissionalDisciplina);

            db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, profDisc.IdProfissional);
            db.AddParam("@ID_PROFISSIONAL_TITULACAO", SqlDbType.Int, profDisc.IdProfissionalTitulacao);
            db.AddParam("@ID_TURMA", SqlDbType.Int, profDisc.IdTurma);
            db.AddParam("@ID_MATRIZ_CURRICULAR", SqlDbType.Int, profDisc.IdMatrizCurricular);
            db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, profDisc.IdDisciplina);
            //return db.ExecuteNonQuery();

            bool excluiu = db.ExecuteNonQuery();

            if (excluiu)
            {
                SQL = @"INSERT INTO TB_AUDITORIA
                      (
                        ACAO,
                        TABELA,
                        DESCRICAO,
                        USUARIO,
                        DATA                                    
                      )
                    VALUES
                      (                                     
                        'DELETE',
                        'TB_PROFISSIONAL_DISCIPLINA',
                        (CAST(@ID_PROFISSIONAL_DISCIPLINA AS VARCHAR)
                        + ' | ' + CAST(@ID_PROFISSIONAL AS VARCHAR)
                        + ' | ' + CAST(@ID_PROFISSIONAL_TITULACAO AS VARCHAR)
                        + ' | ' + CAST(@ID_TURMA AS VARCHAR)
                        + ' | ' + CAST(@ID_MATRIZ_CURRICULAR AS VARCHAR)
                        + ' | ' + CAST(@ID_DISCIPLINA AS VARCHAR)),
                        UPPER(@USUARIO),
                        GETDATE()
                      )";

                db = new DataAccess(SQL);
                db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, profDisc.IdProfissionalDisciplina);
                db.AddParam("@ID_PROFISSIONAL", SqlDbType.Int, profDisc.IdProfissional);
                db.AddParam("@ID_PROFISSIONAL_TITULACAO", SqlDbType.Int, profDisc.IdProfissionalTitulacao);
                db.AddParam("@ID_TURMA", SqlDbType.Int, profDisc.IdTurma);
                db.AddParam("@ID_MATRIZ_CURRICULAR", SqlDbType.Int, profDisc.IdMatrizCurricular);
                db.AddParam("@ID_DISCIPLINA", SqlDbType.Int, profDisc.IdDisciplina);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuario);
            }

            db.ExecuteNonQuery();
            return excluiu;
            return db.ExecuteNonQuery();

        }
        #endregion

        #region Cadastrar Valor Titulação
        public bool inserirValorTitulacao(ProfissionalDisciplina profDisc)
        {
            string SQL = @"UPDATE   TB_PROFISSIONAL_DISCIPLINA
                                    SET    VALOR                          = @VALOR,
                                           DATA_CADASTRO_VALOR            = GETDATE()
                                    WHERE  ID_PROFISSIONAL_DISCIPLINA     = @ID_PROFISSIONAL_DISCIPLINA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@VALOR", SqlDbType.Decimal, profDisc.Valor);
            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, profDisc.IdProfissionalDisciplina);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Lista Disciplinas do aluno Por Professor
        public DataTable listaDisciplinasAlunoProfessor(Turma turma, int matricula)
        {
            string SQL = @"SELECT td.DESCRICAO  AS NOME_DISCIPLINA,
                                   tpt.DESCRICAO_TITULACAO,
                                   TP.NOME       AS PROFISSIONAL,
                                   tpd.VALOR     AS valor_titulacao,
                                   tpd.ID_PROFISSIONAL_DISCIPLINA,
                                   tp.ID_PROFISSIONAL,
                                   tpd.ID_PROFISSIONAL_TITULACAO,
                                   tt.ID_TURMA,
                                   tmc2.ID_MATRIZ_CURRICULAR,
                                   td.ID_DISCIPLINA,
                                   tmc2.CH_DISCIPLINA,
                                   tmc.ID_MODULO_CURRICULO,
                                   tmd.ID_MATRICULA,
                                   tmd.MATRICULA,
                                   tmd.FALTA1,
                                   tmd.NOTA1,
                                   tmd.NOTA2,
                                   tmd.ID_MATRICULA_DISCIPLINA,
                                   tt.COD_TURMA,TMD.SITUACAO
                            FROM   TB_TURMAS tt
                                   INNER JOIN TB_MODULO_CURRICULO tmc
                                        ON  tmc.ID_CURRICULO = tt.ID_CURRICULO
                                        AND tmc.ID_MODULO = tt.ID_MODULO
                                   INNER JOIN TB_MATRIZ_CURRICULAR tmc2
                                        ON  tmc2.ID_MODULO_CURRICULO = tmc.ID_MODULO_CURRICULO
                                   INNER JOIN TB_DISCIPLINA td
                                        ON  td.ID_DISCIPLINA = tmc2.ID_DISCIPLINA
                                   INNER JOIN TB_MATRICULA_DISCIPLINA tmd
                                        ON  tmd.ID_TURMA = tt.ID_TURMA
                                        AND tmd.ID_MATRIZ_CURRICULAR = tmc2.ID_MATRIZ_CURRICULAR
                                        AND tmd.ID_DISCIPLINA = td.ID_DISCIPLINA
                                   LEFT JOIN TB_PROFISSIONAL_DISCIPLINA tpd
                                        ON  tt.ID_TURMA = tpd.ID_TURMA
                                        AND tpd.ID_DISCIPLINA = td.ID_DISCIPLINA
                                   LEFT JOIN TB_PROFISSIONAL_TITULACAO tpt
                                        ON  tpt.ID_PROFISSIONAL_TITULACAO = tpd.ID_PROFISSIONAL_TITULACAO
                                   LEFT JOIN TB_PROFISSIONAL tp
                                        ON  tpd.ID_PROFISSIONAL = tp.ID_PROFISSIONAL
                            WHERE  tt.ID_TURMA = @ID_TURMA
                                   AND tmd.MATRICULA = @MATRICULA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_TURMA", SqlDbType.Int, turma.IdTurma);
            db.AddParam("@MATRICULA", SqlDbType.Int, matricula);
            return db.GetDataTable();
        }
        #endregion
        
        #region Lancar Nota e Falta
        public bool lancarNotaFalta(Disciplina disciplina, int idTurma, int matricula)
        {
            string SQL = @"EXEC SP_LANCA_NOTA_FALTA_APROV
                                    @FALTA1,
	                                @NOTA1,
	                                @ID_MATRICULA_DISCIPLINA,
	                                @ID_TURMA,
	                                @MATRICULA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRICULA_DISCIPLINA", SqlDbType.Int, disciplina.IdMatriculaDisciplina);
            db.AddParam("@NOTA1", SqlDbType.Decimal, disciplina.Nota1);
            db.AddParam("@FALTA1", SqlDbType.Int, disciplina.Falta1);
            db.AddParam("@ID_TURMA", SqlDbType.Int, idTurma);
            db.AddParam("@MATRICULA", SqlDbType.Int, matricula);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Retorna Limite falta
        public int retornaLimiteFaltas(int idMatrizCurricular)
        {
            string SQL = @"DECLARE @LIMITEFALTAS INT = CEILING(
	                        0.25 * (
	                            SELECT ISNULL(TMC.CH_DISCIPLINA,0)
	                            FROM   TB_MATRIZ_CURRICULAR tmc
	                            WHERE  tmc.ID_MATRIZ_CURRICULAR = @ID_MATRIZ_CURRICULAR
	                        )
	                    )
                        SELECT @LIMITEFALTAS";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_MATRIZ_CURRICULAR", SqlDbType.Int, idMatrizCurricular);
            DataTable dt = db.GetDataTable();
            return Convert.ToInt32(dt.Rows[0][0].ToString());

        }
        #endregion

        #region Lista Calendário por Polo
        public DataTable listaCalendarioPolo(int idPolo)
        {
            string SQL = @"SELECT  tc.ID_CALENDARIO,
                                   tc.ID_POLO,
                                   CONVERT(VARCHAR,tc.DATA_AULA,103) AS DATA_AULA,
                                   tc.SITUACAO
                            FROM   TB_CALENDARIO tc
                            WHERE  tc.SITUACAO = 'A'
                            AND tc.ID_POLO = @ID_POLO";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_POLO", SqlDbType.Int, idPolo);
            return db.GetDataTable();
        }
        #endregion

        #region Liberar/Bloquear Disciplina
        public bool liberarBloquearDisciplina(int idProfDisciplina, string libBloq)
        {
            string SQL = @"UPDATE TB_PROFISSIONAL_DISCIPLINA
                            SET    LIBERAR_MATRICULA              = @LIBERAR_MATRICULA,
                                   DATA_LIBERAR_MATRICULA         = @DATA_LIBERAR_MATRICULA
                            WHERE  ID_PROFISSIONAL_DISCIPLINA     = @ID_PROFISSIONAL_DISCIPLINA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@LIBERAR_MATRICULA", SqlDbType.Char, libBloq);

            if(libBloq == "S")
                db.AddParam("@DATA_LIBERAR_MATRICULA", SqlDbType.DateTime, DateTime.Now); 
            else
                db.AddParam("@DATA_LIBERAR_MATRICULA", SqlDbType.DateTime, DBNull.Value); 

            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, idProfDisciplina); 
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Atualizar Data Recepção Diário
        public bool receberDiario(int idProfDisciplina)
        {
            string SQL = @"UPDATE TB_PROFISSIONAL_DISCIPLINA
                                    SET	
	                                    DATA_RECEPCAO_DIARIO = GETDATE()
                                    WHERE ID_PROFISSIONAL_DISCIPLINA = @ID_PROFISSIONAL_DISCIPLINA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, idProfDisciplina);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Lista Cronograma 
        public DataTable listaCronograma(int idProfDisciplina)
        {
//            string SQL = @"SELECT  ROW_NUMBER() OVER(ORDER BY DATA_AULA) AS 'NUMERO_AULA',
//                                   TC.ID_CRONOGRAMA,
//                                   TC.ID_PROFISSIONAL_DISCIPLINA,
//                                   CONVERT(VARCHAR, TC.DATA_AULA, 103) AS DATA_AULA,
//                                   TC.CONTEUDO_AULA,
//                                   TC.DESCRICAO_AULA,
//                                   (
//                                       SELECT COUNT(tf.ID_FREQUENCIA)
//                                       FROM   TB_FREQUENCIA tf
//                                       WHERE  tf.ID_CRONOGRAMA = tc.ID_CRONOGRAMA
//                                   ) AS QTD_FREQUENCIA
//                            FROM   TB_CRONOGRAMA tc
//                            WHERE  tc.ID_PROFISSIONAL_DISCIPLINA = @ID_PROFISSIONAL_DISCIPLINA";
            string SQL = @"SELECT   ROW_NUMBER() OVER(ORDER BY tc2.DATA_AULA) AS 'NUMERO_AULA',
                                    TC.ID_CRONOGRAMA,
                                    TC.ID_PROFISSIONAL_DISCIPLINA,
                                    TC.CONTEUDO_AULA,
                                    TC.DESCRICAO_AULA,
                                    (
                                        SELECT COUNT(tf.ID_FREQUENCIA)
                                        FROM   TB_FREQUENCIA tf
                                        WHERE  tf.ID_CRONOGRAMA = tc.ID_CRONOGRAMA
                                    )              AS QTD_FREQUENCIA,
                                   CONVERT(VARCHAR,tc2.DATA_AULA,103) AS DATA_AULA, th.HORA_INICIO, th.HORA_FIM        
                                    FROM   TB_CRONOGRAMA tc
                                           INNER JOIN TB_CALENDARIO_HORARIO tch
                                                ON  tch.ID_CALENDARIO_HORARIO = tc.ID_CALENDARIO_HORARIO
                                           INNER JOIN TB_CALENDARIO tc2
                                                ON  tc2.ID_CALENDARIO = tch.ID_CALENDARIO
                                           INNER JOIN TB_HORARIO th
                                                ON  th.ID_HORARIO = tch.ID_HORARIO
                            WHERE  tc.ID_PROFISSIONAL_DISCIPLINA = @ID_PROFISSIONAL_DISCIPLINA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, idProfDisciplina);
            return db.GetDataTable();
        }
        #endregion

        #region Inserir Cronograma
        public bool inserirCronograma(DateTime dtAula, string descAula, string conteudoAula, int idProfissionalDisciplina)
        {
            string SQL = @"INSERT INTO TB_CRONOGRAMA
                            (
                            ID_PROFISSIONAL_DISCIPLINA,
                            DATA_AULA,
                            DESCRICAO_AULA,
                            CONTEUDO_AULA
                            )
                        VALUES
                            (
                            @ID_PROFISSIONAL_DISCIPLINA,  
                            @DATA_AULA,
                            @DESCRICAO_AULA,
                            @CONTEUDO_AULA
                            ) ";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, idProfissionalDisciplina);
            db.AddParam("@DATA_AULA", SqlDbType.DateTime, dtAula);
            db.AddParam("@DESCRICAO_AULA", SqlDbType.VarChar, descAula);
            db.AddParam("@CONTEUDO_AULA", SqlDbType.VarChar, conteudoAula);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Alterar Cronograma
        public bool alterarCronograma(int idCronograma, DateTime dtAula, string descAula, string conteudoAula)
        {
            string SQL = @"UPDATE   TB_CRONOGRAMA
                                    SET    --DATA_AULA          = @DATA_AULA,
                                           DESCRICAO_AULA     = @DESCRICAO_AULA,
                                           CONTEUDO_AULA      = @CONTEUDO_AULA
                                    WHERE  ID_CRONOGRAMA      = @ID_CRONOGRAMA ";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CRONOGRAMA", SqlDbType.Int, idCronograma);
            //db.AddParam("@DATA_AULA", SqlDbType.DateTime, dtAula);
            db.AddParam("@DESCRICAO_AULA", SqlDbType.VarChar, descAula);
            db.AddParam("@CONTEUDO_AULA", SqlDbType.VarChar, conteudoAula);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Excluir Cronograma
        public bool excluirCronograma(int idCronograma)
        {
            string SQL = @"DELETE FROM TB_CRONOGRAMA WHERE ID_CRONOGRAMA = @ID_CRONOGRAMA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CRONOGRAMA", SqlDbType.Int, idCronograma);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Incluir Frequência
        public bool incluirFrequencia(int idProfDisciplina)
        {
            string SQL = @"INSERT INTO TB_FREQUENCIA
                            (ID_CRONOGRAMA, MATRICULA)
                            SELECT tc.ID_CRONOGRAMA,
                                   tmd.MATRICULA
                            FROM   TB_CRONOGRAMA tc
                                   INNER JOIN TB_PROFISSIONAL_DISCIPLINA tpd
                                        ON  tpd.ID_PROFISSIONAL_DISCIPLINA = tc.ID_PROFISSIONAL_DISCIPLINA
                                   INNER JOIN TB_MATRICULA_DISCIPLINA tmd
                                        ON  tmd.ID_TURMA = tpd.ID_TURMA
                                        AND tmd.ID_MATRIZ_CURRICULAR = tpd.ID_MATRIZ_CURRICULAR
                                        AND tmd.ID_DISCIPLINA = tpd.ID_DISCIPLINA
                                   INNER JOIN TB_ALUNO ta
                                        ON  ta.MATRICULA = tmd.MATRICULA
                            WHERE  tpd.ID_PROFISSIONAL_DISCIPLINA = @ID_PROFISSIONAL_DISCIPLINA";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, idProfDisciplina);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Excluir Frequencia
        public bool excluirFrequencia(int idCronograma)
        {
            string SQL = @"DELETE 
                            FROM   TB_FREQUENCIA
                            WHERE  ID_CRONOGRAMA = @ID_CRONOGRAMA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CRONOGRAMA", SqlDbType.Int, idCronograma);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Atualizar Frequencia
        public bool atualizarFrequencia(int idFrequencia, char situacao)
        {
            string SQL = @"UPDATE TB_FREQUENCIA
                            SET    SITUACAO          = @SITUACAO
                            WHERE  ID_FREQUENCIA     = @ID_FREQUENCIA";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_FREQUENCIA", SqlDbType.Int, idFrequencia);
            db.AddParam("@SITUACAO", SqlDbType.Char, situacao);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Listar Frequencia
        public DataTable listarFrequencia(int idCronograma)
        {
            string SQL = @" SELECT TF.ID_FREQUENCIA,
                                   TF.ID_CRONOGRAMA,
                                   TF.MATRICULA,
                                   TF.SITUACAO, TA.NOME
                            FROM   TB_FREQUENCIA tf
                                   INNER JOIN TB_ALUNO ta
                                        ON  ta.MATRICULA = tf.MATRICULA
                            WHERE  tf.ID_CRONOGRAMA = @ID_CRONOGRAMA
                            ORDER BY TA.NOME";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_CRONOGRAMA", SqlDbType.Int, idCronograma);            
            return db.GetDataTable();
        }
        #endregion

        #region Carregar dados do Profissional da Disciplina
        public ProfissionalDisciplina carregarDadosProfDisciplina(int IdProfissionalDisciplina)
        {
            #region Script
            string SQL = @"SELECT   tp.ID_PROFISSIONAL_DISCIPLINA,
                                    tp.ID_PROFISSIONAL,
                                    tp.ID_PROFISSIONAL_TITULACAO,
                                    tp.ID_TURMA,
                                    tp.ID_MATRIZ_CURRICULAR,
                                    tp.ID_DISCIPLINA
                                FROM   TB_PROFISSIONAL_DISCIPLINA tp
                                WHERE tp.ID_PROFISSIONAL_DISCIPLINA = @ID_PROFISSIONAL_DISCIPLINA";
            #endregion

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_PROFISSIONAL_DISCIPLINA", SqlDbType.Int, IdProfissionalDisciplina);

            try
            {
                ProfissionalDisciplina profDisc = new ProfissionalDisciplina();
                db.AbrirConexao();
                SqlDataReader dr = db.ExecuteReader();

                if (dr.Read())
                {
                    profDisc.IdProfissionalDisciplina = Convert.ToInt32(dr["ID_PROFISSIONAL_DISCIPLINA"]);
                    profDisc.IdProfissional = Convert.ToInt32(dr["ID_PROFISSIONAL"]);
                    profDisc.IdProfissionalTitulacao = Convert.ToInt32(dr["ID_PROFISSIONAL_TITULACAO"]);
                    profDisc.IdTurma = Convert.ToInt32(dr["ID_TURMA"]);
                    profDisc.IdMatrizCurricular = Convert.ToInt32(dr["ID_MATRIZ_CURRICULAR"]);
                    profDisc.IdDisciplina = Convert.ToInt32(dr["ID_DISCIPLINA"]);
                }

                return profDisc;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Cronograma de Aulas por Data
        public DataTable listaCronogramaAulasPorData(int idPolo, DateTime dtAula)
        {
            string SQL = @"SELECT  tc3.DESCRICAO  AS curso,
                                   tc2.CURRICULO,
                                   tm.DESCRICAO   AS modulo,
                                   tt.COD_TURMA,
                                   td.DESCRICAO   AS disciplina,
                                   tp.NOME        AS docente,
                                   tp2.NOME       AS coordenador,
                                   CASE tpd.LIBERAR_MATRICULA
                                        WHEN 'N' THEN 'Não Confirmada'
                                        ELSE 'Confirmada'
                                   END            AS situacao,
                                   tc.DATA_AULA
                            FROM   TB_CRONOGRAMA tc
                                   INNER JOIN TB_PROFISSIONAL_DISCIPLINA tpd
                                        ON  tpd.ID_PROFISSIONAL_DISCIPLINA = tc.ID_PROFISSIONAL_DISCIPLINA
                                   INNER JOIN TB_TURMAS tt
                                        ON  tt.ID_TURMA = tpd.ID_TURMA
                                   INNER JOIN TB_MODULO_CURRICULO tmc
                                        ON  tmc.ID_CURRICULO = tt.ID_CURRICULO
                                        AND tmc.ID_MODULO = tt.ID_MODULO
                                   INNER JOIN TB_CURRICULO tc2
                                        ON  tc2.ID_CURRICULO = tt.ID_CURRICULO
                                   INNER JOIN TB_CURSO tc3
                                        ON  tc3.ID_CURSO = tc2.ID_CURSO
                                   INNER JOIN TB_MODULO tm
                                        ON  tm.ID_MODULO = tt.ID_MODULO
                                   INNER JOIN TB_DISCIPLINA td
                                        ON  td.ID_DISCIPLINA = tpd.ID_DISCIPLINA
                                   INNER JOIN TB_PROFISSIONAL tp
                                        ON  tp.ID_PROFISSIONAL = tpd.ID_PROFISSIONAL
                                   INNER JOIN TB_PROFISSIONAL tp2
                                        ON  tp2.ID_PROFISSIONAL = tt.ID_PROFISSIONAL
                            WHERE  TC.DATA_AULA = @DATA_AULA
                                   AND (tt.ID_POLO = @ID_POLO OR 0 = @ID_POLO)";

            DataAccess db = new DataAccess(SQL);
            db.AddParam("@DATA_AULA", SqlDbType.DateTime, dtAula);
            db.AddParam("@ID_POLO", SqlDbType.Int, idPolo);
            return db.GetDataTable();
        }
        #endregion
    }
}

